package cj.web.admin.dao;
/*
 *  
 *  
*/
import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.admin.domain.UserInfo;

/**
 * 
 * @author 
 *
 */
@DAO
public interface UserInfoDAO{
	
	@SQL("select a.*,GROUP_CONCAT(c.name) as roleName from (SELECT id,photo,account,name,descs,pwd,phone,email,type,state,createUser,date_format(createAT,'%Y-%m-%d %H:%i:%s') as createAT,updateUser,date_format(updateAT,'%Y-%m-%d %H:%i:%s') as updateAT  FROM UserInfo WHERE deleteFlag=1  #if(:t.account!=''){and ( account like '%##(:t.account)%' and name like '%##(:t.name)%' and phone like '%##(:t.phone)%') }  ORDER BY updateAT DESC LIMIT :t.start,:t.length ) a left join userRoleRef b on a.id=b.userId left join roleInfo c on b.roleId=c.id group by a.id")
    List<Map> query(@SQLParam("t") UserInfo userInfo);
    
	@SQL("SELECT count(id) FROM UserInfo WHERE deleteFlag=1  #if(:t.account!=''){and ( account like '%##(:t.account)%' and name like '%##(:t.name)%' and phone like '%##(:t.phone)%') } ")
    int querysize(@SQLParam("t") UserInfo userInfo);
	
	@SQL("select count(id) from UserInfo where account=:1")
	int queryByAccount(String account);
	
	@SQL("select * from UserInfo where account=:1")
	UserInfo queryByAP(String account);
	
    @SQL("select * from UserInfo where id=:1")
    UserInfo queryById(String id);
    
    @SQL("select a.name as memuname from MemuInfo a join RoleMenuRef b on a.id=b.menuId join RoleInfo c on b.roleId=c.id join UserRoleRef d on c.id=d.roleId join UserInfo e on e.id = d.userId where e.id=:1 and a.deleteFlag=1")
    List<String> queryByMemu(String id);
    
    @SQL("INSERT INTO UserInfo ("
        		+ "id,"
        		+ "photo,"
        		+ "account,"
        		+ "name,"
        		+ "descs,"
        		+ "pwd,"
        		+ "phone,"
        		+ "email,"
        		+ "type,"
        		+ "state,"
        		+ "deleteFlag,"
        		+ "createUser,"
        		+ "createAT,"
        		+ "updateUser,"
        		+ "updateAT"
				+") VALUES ("
				+ ":t.id,"
				+ ":t.photo,"
				+ ":t.account,"
				+ ":t.name,"
				+ ":t.descs,"
				+ ":t.pwd,"
				+ ":t.phone,"
				+ ":t.email,"
				+ ":t.type,"
				+ ":t.state,"
				+ ":t.deleteFlag,"
				+ ":t.createUser,"
				+ "now(),"
				+ ":t.updateUser,"
				+ "now()"
			+")")
    void create(@SQLParam("t") UserInfo userInfo);
	
	@SQL("UPDATE UserInfo SET "
				+ "photo=:t.photo,"
		        + "account=:t.account,"
		        + "name=:t.name,"
		        + "descs=:t.descs,"
		        + "pwd=:t.pwd,"
		        + "phone=:t.phone,"
		        + "email=:t.email,"
		        + "updateUser=:t.updateUser,"
		        + "updateAT=now()"
				+ " WHERE id=:t.id")
    void update(@SQLParam("t") UserInfo userInfo);
    
    @SQL("UPDATE UserInfo SET deleteFlag=0 WHERE id=:1")
    void deleteById(String id);
}